package cn.ainit.sys.framework.upload;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import org.hibernate.dialect.function.VarArgsSQLFunction;
import org.junit.Test;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import sun.misc.BASE64Encoder;

import cn.ainit.sys.common.dao.SysBaseDao;
import cn.ainit.sys.menu.SessionCont;
import cn.ainit.sys.utils.UUID32;

@Transactional
@Repository
public class UploadDaoImpl extends SysBaseDao  implements UploadDao{

	@Override
	public List<Map<String, Object>> loadAllImgs(String dsId, String clnName,
			String pkValue) throws Exception {
		String sql = " SELECT * FROM SYS_UPLOAD_FILES WHERE DS_ID = '"+dsId+"' AND CLN_NAME = '"+clnName+"' AND DJ_KEY_VALUE = '"+pkValue+"' ORDER BY UPLOAD_DATE DESC";
		List<Map<String, Object>> imgs = this.getJdbcTemplate().queryForList(sql);
		return imgs;
	}

	@Override
	public boolean saveImgPath(SysUploadInfo uploadInfo) throws Exception {
		String sql = " INSERT INTO SYS_UPLOAD_FILES (ID,DS_ID,CLN_NAME,IS_DEFAULT,NAME,DESCRIBE,FILE_SIZE,SUFFIX,PATH,UPLOAD_O_ID,UPLOAD_DATE,DJ_KEY_VALUE) "
			+"VALUES ('"+UUID32.newGuid()
			+"','"
			+uploadInfo.getDsId()+"','"
			+uploadInfo.getClnName()+"','','"
			+uploadInfo.getName()+"','"
			+uploadInfo.getDescribe()+"','','"
			+uploadInfo.getSuffix()+"','"
			+uploadInfo.getPath()+"','',?,'"
			+uploadInfo.getKeyValue()+"')";
		Connection conn = null;
		PreparedStatement stmt = null;
		try {
			conn = this.getJdbcTemplate().getDataSource().getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
			stmt.execute();
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if (stmt != null) {
				stmt.close();
			}
			if (conn!=null) {
				conn.close();
			}
		}
		return true;
	}
	/**
	 * 保存文件到文件夹
	 */
	@Override
	public boolean saveFiles(SysUploadInfo uploadInfo) throws Exception {
		System.out.println("DSID"+uploadInfo.getDsId());
		System.out.println("CLNname"+uploadInfo.getClnName());
		System.out.println("ufileName"+uploadInfo.getName());
		System.out.println("suffix"+uploadInfo.getSuffix());
		System.out.println("path"+uploadInfo.getPath());
		System.out.println(""+uploadInfo.getKeyValue());
		String sql = " INSERT INTO SYS_UPLOAD_FILES_BLOB (ID,DS_ID,CLN_NAME,IS_DEFAULT,NAME,DESCRIBE,FILE_SIZE,SUFFIX,PATH,FILE_STREAM,UPLOAD_O_ID,UPLOAD_DATE,DJ_KEY_VALUE) "
			+"VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)";
		Connection conn = null;
		PreparedStatement stmt = null;
		Timestamp sTimestamp = new Timestamp(System.currentTimeMillis());
		System.out.println(sTimestamp.toString());
		try {
			String fileStream = "";
			conn = this.getJdbcTemplate().getDataSource().getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, UUID32.newGuid());
			stmt.setString(2, uploadInfo.getDsId());
			stmt.setString(3, uploadInfo.getClnName());
			stmt.setString(4, "");//IS_DEFAULT
			stmt.setString(5, uploadInfo.getName());
			stmt.setString(6, uploadInfo.getDescribe());
			stmt.setString(7, "");//FILE_SIZE
			stmt.setString(8, uploadInfo.getSuffix());
			stmt.setString(9, uploadInfo.getPath());
			stmt.setString(10, fileStream);
			stmt.setString(11, "");
			stmt.setTimestamp(12, new Timestamp(System.currentTimeMillis()));
			stmt.setString(13, uploadInfo.getKeyValue());
			stmt.execute();
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if (stmt != null) {
				stmt.close();
			}
			if (conn!=null) {
				conn.close();
			}
		}
		return true;
	}

	@Override
	public String saveFilesToDb(SysUploadInfo uploadInfo) throws Exception {
		System.out.println("DSID"+uploadInfo.getDsId());
		System.out.println("CLNname"+uploadInfo.getClnName());
		System.out.println("ufileName"+uploadInfo.getName());
		System.out.println("suffix"+uploadInfo.getSuffix());
		System.out.println("path"+uploadInfo.getPath());
		System.out.println(""+uploadInfo.getKeyValue());

		String sql = " INSERT INTO SYS_UPLOAD_FILES_BLOB (ID,DS_ID,CLN_NAME,IS_DEFAULT,NAME,DESCRIBE,FILE_SIZE,SUFFIX,PATH,FILE_STREAM,UPLOAD_O_ID,UPLOAD_DATE,DJ_KEY_VALUE) "
			+"VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)";
		Connection conn = null;
		PreparedStatement stmt = null;
		Timestamp sTimestamp = new Timestamp(System.currentTimeMillis());
		System.out.println(sTimestamp.toString());
		String uuid32 = UUID32.newGuid();
		try {
			conn = this.getJdbcTemplate().getDataSource().getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setString(1,	uuid32);
			stmt.setString(2, uploadInfo.getDsId());
			stmt.setString(3, uploadInfo.getClnName());
			stmt.setString(4, "");//IS_DEFAULT
			stmt.setString(5, uploadInfo.getName());
			stmt.setString(6, uploadInfo.getDescribe());
			stmt.setString(7, "");//FILE_SIZE
			stmt.setString(8, uploadInfo.getSuffix());
			stmt.setString(9, uploadInfo.getPath());
			//文件需要加密
			String realPath = uploadInfo.getRequest().getRealPath("")+uploadInfo.getPath();
			File file =  new File(realPath);
			FileInputStream fStream = new FileInputStream(file);
			stmt.setBinaryStream(10, fStream, (int) file.length());//保存文件流
			//			stmt.setString(10, fileStream);

			stmt.setString(11, "");
			stmt.setTimestamp(12, new Timestamp(System.currentTimeMillis()));
			stmt.setString(13, uploadInfo.getKeyValue());
			stmt.execute();
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if (stmt != null) {
				stmt.close();
			}
			if (conn!=null) {
				conn.close();
			}
		}
		return uuid32;
	}

	@Override
	public SysFilesList getFileList(SysFilesList fileList) throws Exception {
		String sql = " select ID,DS_ID,CLN_NAME,NAME,DESCRIBE,SUFFIX,PATH,UPLOAD_DATE from SYS_UPLOAD_FILES_BLOB where " +
		" DS_ID =  '"+fileList.getDsId()+"' " +
		" AND CLN_NAME = '"+fileList.getClnName()+"' " +
		" AND DJ_KEY_VALUE =  '"+fileList.getKeyValue()+"' ORDER BY UPLOAD_DATE DESC ";

		fileList.setFileList(this.getJdbcTemplate().queryForList(sql));
		return fileList;
	}
	/**
	 * 删除文件
	 */
	@Override
	public boolean delFile(SysUploadInfo delFile) throws Exception {
		String sql = " DELETE FROM SYS_UPLOAD_FILES_BLOB WHERE ID = '"+delFile.getFileid()+"' " +
		" AND DS_ID = '"+delFile.getDsId()+"' AND CLN_NAME = '"+delFile.getClnName()+"'";
		try {
			this.getJdbcTemplate().execute(sql);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}

	/**
	 * 下载文件
	 */
	@Override
	public SysUploadInfo downFile(SysUploadInfo downFile) throws Exception {
		String sql = " SELECT * FROM SYS_UPLOAD_FILES_BLOB WHERE ID = '"+downFile.getFileid()+"' " +
		" AND DS_ID = '"+downFile.getDsId()+"' AND CLN_NAME = '"+downFile.getClnName()+"'";
		Map<String, Object> fillMap = null;
		SysUploadInfo downInfo = new SysUploadInfo();
		Connection conn = null;
		Statement stmt = null;
		byte[] data = null; 
		try {
			conn = this.getJdbcTemplate().getDataSource().getConnection();
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			if (rs.next()) {  
				java.sql.Blob blob = rs.getBlob("FILE_STREAM");  
				InputStream inStream = blob.getBinaryStream();
				try {  
					long nLen = blob.length();  
					int nSize = (int) nLen;  
					data = new byte[nSize];  
					inStream.read(data);  
					inStream.close();  
				} catch (Exception e) {  
					System.out.println("获取图片数据失败,原因:" + e.getMessage());  
				}
				downInfo.setName(rs.getString("NAME"));
				downInfo.setSuffix(rs.getString("SUFFIX"));
			}
			downInfo.setFileByte(data);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if (stmt != null) {
				stmt.close();
			}
			if (conn != null) {
				conn.close();
			}
		}
		return downInfo;
	}

	@Override
	public Integer getWCount(String pkValue) throws Exception {
		String sql = " SELECT COUNT(1) FROM SYS_FORUM_COUNT where DJ_CL_ID = '"+pkValue+"' ";
		return this.getJdbcTemplate().queryForInt(sql);
	}

	@Override
	public List<WeiBoObj> loadWeiBo(String pkValue) throws Exception {
		return null;
	}


	/******
	 * 上传图片点击保存的时候把图片从临时文件转到正式的文件夹
	 * 并且把图片的路径存到数据库中
	 */
	@Override
	public Map<String,String> uploadTempImgs(String dsId,String colnName,String uid,HttpSession session,HttpServletRequest request){
		Map<String, String> map = new HashMap<String, String>();
		//主键从图片名称上截取;
		String pkValue = UUID32.newGuid();
		// 获取附加信息
		String folder =  "UploadFiles\\temporary\\"+dsId+"-"+uid;
		String ctxPath = request.getSession().getServletContext().getRealPath("/");
		File fileZhenshi = new File(ctxPath + "UploadFiles\\permanent\\");
		File files = new File(ctxPath + folder);
		
		File[] tempList = files.listFiles();
		File[] filesZhenshis = fileZhenshi.listFiles();
		String error = "";
		Boolean isDouble = true;
		if(tempList!=null && tempList.length>0){
			for (int i = 0; i < tempList.length; i++) {
				if(tempList[i].getName().split("-")[0].equals(colnName))
				{ 
				//临时文件-->>正式文件查重
				for (int j = 0; j < filesZhenshis.length; j++) {
					if (tempList[i].getName().equals(filesZhenshis[j].getName())) {
						error += tempList[i].getName()+",";
						isDouble = false;
					}
				}
				if (isDouble) {
					try {
						FileInputStream in = new FileInputStream(new File(
								tempList[i].toString()));// 指定要读取的图片
						File file = new File(ctxPath + "UploadFiles\\permanent\\");
						if (!file.exists()) {// 如果文件夹不存在，则创建该文件夹
							file.mkdir();
						}
						FileOutputStream out = new FileOutputStream(new File(ctxPath + "UploadFiles\\permanent\\" + tempList[i].getName()));// 指定要写入的图片
						int n = 0;// 每次读取的字节长度
						byte[] bb = new byte[1024];// 存储每次读取的内容
						while ((n = in.read(bb)) != -1) {
							out.write(bb, 0, n);// 将读取的内容，写入到输出流当中
						}
						out.close();// 关闭输入输出流
						in.close();
					} catch (Exception e) {
						e.printStackTrace();
					}
					String lasttype = tempList[i].getName().substring(tempList[i].getName().lastIndexOf("."));
					String [] imgName = tempList[i].getName().split("-");
					
					///将图片信息
					SysUploadInfo info = new SysUploadInfo();
					info.setKeyValue(pkValue);
					info.setDsId(dsId);
					info.setName(tempList[i].getName());
					info.setSuffix(lasttype);
					info.setPath("UploadFiles//permanent//" + tempList[i].getName());
					info.setClnName(imgName[0]);
					try {
						savePictureInfo(info,uid);   //把临时文件中的
					}catch (Exception e) {
						e.printStackTrace();
					}
				}
				isDouble = true;
			}
			}
		}
		map.put("pkValue",pkValue);
		map.put("error", error);
		return map;
	}


	/**
	 * 图片信息进入数据库
	 */
	@Override
	public void savePictureInfo(SysUploadInfo sysUploadInfo,String uid)throws Exception {
		String sql="INSERT INTO SYS_UPLOAD_FILES_BLOB (ID,DS_ID,CLN_NAME,IS_DEFAULT,NAME,DESCRIBE,FILE_SIZE,SUFFIX,PATH,UPLOAD_O_ID,UPLOAD_DATE,DJ_KEY_VALUE)"
			+"VALUES (?,?,?,?,?,?,?,?,?,?,?,?)";
		Connection conn=null;
		PreparedStatement stmt=null;
		try {
			conn=this.jdbcTemplate.getDataSource().getConnection();
			stmt= conn.prepareStatement(sql);
			stmt.setString(1, UUID32.newGuid());
			stmt.setString(2, sysUploadInfo.getDsId());
			stmt.setString(3, sysUploadInfo.getClnName());
			stmt.setString(4, "");//IS_DEFAULT
			stmt.setString(5, sysUploadInfo.getName());
			stmt.setString(6, "");
			stmt.setString(7, "");//FILE_SIZE
			stmt.setString(8, sysUploadInfo.getSuffix());
			stmt.setString(9, sysUploadInfo.getPath());
			stmt.setString(10, uid);
			stmt.setTimestamp(11, new Timestamp(System.currentTimeMillis()));
			stmt.setString(12, sysUploadInfo.getKeyValue());
			stmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			if (stmt != null) {
				stmt.close();
			}
			if (conn!=null) {
				conn.close();
			}
		}
	}


	@Override
	public void updateLoadImgs(String realkeyVal,String oldVal) throws Exception{
		Connection conn = null;
		PreparedStatement stmt = null;
		String sql="UPDATE sys_upload_files_blob s SET s.Dj_Key_Value=? WHERE s.dj_key_value=?";
		try {
			conn = this.getJdbcTemplate().getDataSource().getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, realkeyVal);
			stmt.setString(2, oldVal);
			stmt.execute();
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("=========#UploadDaoImpl 339===========上传图片的时候更新sys_upload_files_blob表报错，请检查！！！");
		}finally{
			if (stmt != null) {
				stmt.close();
			}
			if (conn!=null) {
				conn.close();
			}
		}
	}


	@Override
	public String deletedPictureById(HttpServletRequest request,
			HttpServletResponse response, HttpSession session) {
		String id = request.getParameter("id");
		//查询出图片名称
		String sql1="SELECT * FROM sys_upload_files_blob  s WHERE s.id='"+ id +"'";
		List<Map<String, Object>> list1 = this.getJdbcTemplate().queryForList(sql1);
		String imgName = list1.get(0).get("NAME").toString();
		String sql2 = " DELETE FROM SYS_UPLOAD_FILES_BLOB WHERE ID = '"+id+"'" ;  
		this.getJdbcTemplate().execute(sql2);
		String ctxPath = request.getSession().getServletContext().getRealPath("/");
		File fileZhenshi = new File(ctxPath + "UploadFiles\\permanent\\"+imgName);
		if(fileZhenshi.exists())
		{
			fileZhenshi.delete();
			return id;
		}
		return "error";
	}


}







